use master
go
create database BBS
on
(
	name='BBS',
	filename='D:\SQL\BBS.mdf',
	size=5,
	maxsize=50,
	filegrowth=5
)
log on
(
	name='BBS_log',
	filename='D:\SQL\BBS_log.ldf',
	size=5,
	maxsize=50,
	filegrowth=5
)
go

use BBS
go 
create table BBSUsers
(
	UsersID int identity(1,1),
	UsersName varchar(10) not null ,
	UsersSex varchar(2) not null ,
	UsersAge int not null,
	UsersPoint int not null
)
alter table BBSUsers add constraint PK_BBSUsers_UsersID primary key(UsersID)
alter table BBSUsers add constraint UK_BBSUsers_UsersID unique(UsersName)
alter table BBSUsers add constraint CK_BBSUsers_UsersSex check(UsersSex in('男','女'))
alter table BBSUsers add constraint CK_BBSUsers_UsersAge check(UsersAge>=15 and UsersAge<=60)
alter table BBSUsers add constraint CK_BBSUsers_UsersPoint check(UsersPoint>=0)

create table BBSSection
(
	SectionID int identity(1,1),
	SectionName varchar(10) not null,
	SectionUid int
)
alter table BBSSection add constraint PK_BBSSection_SectionID primary key (SectionID)
alter table BBSSection add constraint FK_BBSSection_SectionUid foreign key (SectionUid) references BBSUsers(UsersID)

create table BBSTopic
(
	TopicID int primary key identity(1,1),
	TopicUID int references BBSUsers(UsersID),
	TopicSID int references BBSSection(SectionID),
	TopicTitle varchar(100) not null,
	TopicMdg text not null,
	TopicTime datetime ,
	TopicCount int
)

create table BBSReply
(
	ReplyID int primary key identity(1,1),
	ReplyUID int references BBSUsers(UsersID) ,
	ReplyTID int references BBSTopic(TopicID),
	ReplyMsa text not null,
	ReplyTime datetime
)
go

-- test code
select * from BBSPoint
select * from BBSUsers
select * from BBSSection
select * from BBSTopic

-- insert code
insert into BBSUsers(UsersName,UsersSex,UsersAge,UsersPoint) values
('小雨点','女',20,0),
('逍遥','男',18,4),
('七年级生','男',19,2)
go

select UsersName,UsersPoint into BBSPoint from BBSUsers
go

insert into BBSSection(SectionName,SectionUid) values
('技术交流',1),
('读书世界',3),
('生活百科',1),
('八卦区',3)
go

insert into BBSTopic values
(2,4,'范跑跑','范跑跑是谁？','2008-7-8',1),
(3,1,'.NET','.NET与JAVA有什么区别','2008-9-1',2),
(1,3,'今年夏天流行什么','今年夏天流行什么啊？','2021-3-16',0)
go

insert into BBSReply values
(1,1,'是憨批','2021-3-16'),
(2,2,'啊这','2022-2-22'),
(3,3,'流行崇拜懂王','2017-7-16')
go

-- 1.查询出每个版块的版主编号，版主姓名和版块名称
select
    b.UsersID 版主ID , a.UsersName 版主姓名 , a.SectionName 版块名称 
from 
    BBSSection a 
join
    BBSUsers b 
on 
    a.SectionUid = b.UsersID 

-- 2.查询出主贴的发帖时间在2008-9-15以后的主贴的发帖人编号，发帖人姓名，帖子的标题，帖子的内容和发帖时间
select 
    b.UsersID 发帖人编号 , a.UsersName 姓名 , a.TopicTitle 标题 , a.TopicMdg 内容 , a.TopicTime 时间 
from 
    BBSTopic a
join 
    BBSUsers b 
on 
    a.TopicUID = b.UsersID 
where 
    a.TopicTime > '2008-9-15'

-- 3.查询出年龄在20以下的版主的编号，版主的名称和版块的名称
select 
    b.UsersID 版主编号 , a.UsersName 版主姓名 , a.SectionName 版块名称 
from 
    BBSSection a 
join 
    BBSUsers b 
on
    a.SectionUid = b.UsersID 
where 
    a.UsersAge < 20

-- 4.查询出回复数量最多的主贴的发帖人编号，发帖人姓名，主贴标题，主贴内容和回复数量
select 
    b.UsersID 发帖人编号, a.UsersName 姓名, a.TopicTitle 主贴标题, a.TopicMdg 主贴内容, a.TopicCount 回复数量 
from 
    BBSTopic a
inner join 
    BBSUsers b
on
     a.TopicUID = b.UsersID 
where
    a.TopicCount = 
    (
    select 
        MAX(a.TopicCount)
     from
       b.BBSTopic
    )

-- 5.在主贴表中查询每个版块中每个用户的发帖总数
select 
    SectionName 版块名字 , UsersName 用户姓名 , COUNT(*) 数量 
from 
    BBSTopic a 
join 
    BBSSection b 
on
    b.SectionUid = a.TopicUID
join
    BBSUsers c
on
    a.TopicUID = c.UsersID 
group by
    UsersName,SectionName